﻿using iTool.SQL.Analysis.Context.Options;
using iTool.SQL.Analysis.Context.SQLWhereOptions;

using Microsoft.SqlServer.Management.SqlParser.SqlCodeDom;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace iTool.SQL.Analysis.Context
{
    public class SqlScriptOptions 
    {
        public SqlScriptOptions() 
        {
            this.ScriptContent = new StringBuilder();
            this.ChildrenQuerys = new List<ChildQueryOptions>();
            this.DependenceFields = new List<SQLFieldOptions>();
        }

        public StringBuilder ScriptContent { get; set; }
        public List<ChildQueryOptions> ChildrenQuerys { get; set; }
        public List<SQLFieldOptions> DependenceFields { get; set; }
        public int ExcuterPriority { get; set; }
    }

    public class SqlSelectAnalysisContext : SqlAnalysisContext
    {
        public SqlBinaryBooleanExpression? Condition { get; set; }
        public bool IsDistinct { get; set; }
        public string TopCount { get; set; }

        public List<SQLTableOptions> QueryTables { get; set; } = new List<SQLTableOptions>();
        public List<SQLFieldOptions> QueryFields { get; set; } = new List<SQLFieldOptions>();

        public SqlSelectAnalysisContext()
        {
            base.SQLAction = SQLActionOptions.SELECT;
        }


        public List<SqlScriptOptions> GetSqlScripts(Dictionary<string, List<string>> tableFieldsMapDictionary = null, List<SqlScriptOptions> result = null)
        {
            result = result ?? new List<SqlScriptOptions>();

            tableFieldsMapDictionary = this.GetTableFieldsMapDictionary(tableFieldsMapDictionary);

            int index = 0;
            foreach (var query in this.QueryTables)
            {
                SqlScriptOptions scriptOptions = new SqlScriptOptions();

                // set title
                scriptOptions.ScriptContent.Append("select ");
                // set distinct
                if (this.IsDistinct) scriptOptions.ScriptContent.Append("distinct ");
                // set top
                if (!string.IsNullOrEmpty(this.TopCount)) scriptOptions.ScriptContent.Append(string.Format("top {0}", this.TopCount));

                // set fields
                this.SetQueryFields(query.Alias, query, scriptOptions.ScriptContent, tableFieldsMapDictionary);

                // set from table
                scriptOptions.ScriptContent.Append("from ");
                scriptOptions.ScriptContent.Append(query.Name);

                // set where
                //this.SetConditions(query.Alias, query.Name, index==0, query, stringBuilder);
                base.SetConditions(query.Alias, query.Name, index == 0, this.GroupWheres, scriptOptions);

                // set group
                if (query.GroupBy != null) this.SetGroupFields(query.Alias, query.Name, query.GroupBy, scriptOptions);

                // set order by
                if (query.OrderBy != null) this.SetOrderFields(query.Alias, query.Name, query.OrderBy, scriptOptions.ScriptContent);

                result.Add(scriptOptions);

                index++;
                Console.WriteLine(scriptOptions.ScriptContent);
            }

            if (this.ChildrenQuerys != null)
                foreach (var query in this.ChildrenQuerys)
                {
                    query.GetSqlScripts(tableFieldsMapDictionary, result);
                }

            return result;
        }


        private void SetQueryFields(string? alias, SQLTableOptions query, StringBuilder stringBuilder, Dictionary<string, List<string>> tableMapDictionary)
        {
            if (this.QueryFields.Any(item => item.Name == "*" && string.IsNullOrEmpty(item.Schema)))
            {
                stringBuilder.Append("* ");
            }
            else
            {
                if (query.JoinOperator != null)
                {
                    stringBuilder.Append(String.Join(',', tableMapDictionary[query.Name]));
                    stringBuilder.Append(' ');
                }
                else
                {
                    foreach (var queryFieldsitem in this.QueryFields)
                    {
                        if (queryFieldsitem.Schema == alias || queryFieldsitem.Schema == query.Name)
                        {
                            stringBuilder.Append($"{queryFieldsitem.ToString()},");
                        }
                    }

                    if (stringBuilder[^1] == ',')
                    {
                        stringBuilder[^1] = ' ';
                    }
                }
            }
        }

        private void SetGroupFields(string? schema, string table, GroupByOptions groupByOptions, SqlScriptOptions scriptOptions) 
        {
            scriptOptions.ScriptContent.Append(' ');
            scriptOptions.ScriptContent.Append("group by ");
            scriptOptions.ScriptContent.Append(String.Join(',', groupByOptions.Fields.Select(item => item.Name)));

            // having
            base.SetConditions(schema, table, true, groupByOptions.GroupWheres, scriptOptions, "having");
        }

        private void SetOrderFields(string? schema, string table, SortByOptions sortByOptions, StringBuilder stringBuilder) 
        {
            stringBuilder.Append(' ');
            stringBuilder.Append("order by ");
            stringBuilder.Append(String.Join(',', sortByOptions.Fields.Select(item => item.Name)));
            if (sortByOptions.SortOrder == SqlSortOrder.Descending)
            {
                stringBuilder.Append(' ');
                stringBuilder.Append("desc");
            }
        }


        protected override Dictionary<string, List<string>> GetTableFieldsMapDictionary(Dictionary<string, List<string>> tableFieldsMapDictionary = null)
        {
            tableFieldsMapDictionary = tableFieldsMapDictionary ?? new Dictionary<string, List<string>>();

            foreach (var item in this.QueryTables)
            {
                if (!tableFieldsMapDictionary.ContainsKey(item.Name))
                {
                    tableFieldsMapDictionary.Add(item.Name, new List<string>());
                }
            }
            foreach (var item in this.Fields)
            {
                if (string.IsNullOrEmpty(item.Schema))
                {
                    tableFieldsMapDictionary.First().Value.Add(item.Name);
                }
                else
                {
                    var table = this.QueryTables.Where(table => table.Alias == item.Schema).FirstOrDefault();
                    tableFieldsMapDictionary[table?.Name ?? item.Schema].Add(item.Name);
                }
            }
            foreach (var item in tableFieldsMapDictionary)
            {
                tableFieldsMapDictionary[item.Key] = item.Value.Distinct().ToList();
            }
            return tableFieldsMapDictionary;
        }

    }
}
